 # -*- coding: utf-8 -*-
import json

def getProvinceCityList(request):
    #1数据
    request.cursor_standard.execute(request.formatSql('''
        select 
            company_name,
            province data_source_province,
            city  data_source_city,
            dict_city.city_name  
        from standard.company_main 
        left join standard.dict_city on company_main.city_code = dict_city.city_code
        where company_main.is_del = 'N'
        and ('${company_name}' = '' or company_name like '%${company_name}%' )
        and ('${data_source_province}' = '' or province like '%${data_source_province}%' )
        and ('${data_source_city}' = '' or city like '%${data_source_city}%' )
        and (
            ('${city_code}' = 'all') or ('${city_code}' = '' and ifNull(company_main.city_code,'') = ''  ) or (company_main.city_code = '${city_code}')
        )
        order by company_main.province desc,company_main.city desc 
        limit ${start} , ${rows} 
    '''))
    rows = request.cursor_standard.fetchall()
    #2页码
    request.cursor_standard.execute(request.formatSql('''
        select count(*) as  total 
        from company_main
        where company_main.is_del = 'N'
        and ('${company_name}' = '' or company_name like '%${company_name}%' )
        and ('${data_source_province}' = '' or province like '%${data_source_province}%' )
        and ('${data_source_city}' = '' or city like '%${data_source_city}%' )
        and (
            ('${city_code}' = 'all') or ('${city_code}' = '' and ifNull(company_main.city_code,'') = ''  ) or (company_main.city_code = '${city_code}')
        )
    '''))
    total = request.cursor_standard.fetchone()['total']
    return json.dumps({'rows':rows,'total':total}, ensure_ascii=False)


def getComboboxAlreadyCity(request):
    request.cursor_standard.execute(request.formatSql('''
        select '' as code,'空' as name
        union 
        select 'all' as code,'所有' as name
        union
        select
            company_main.city_code as code,
            dict_city.city_name as name
        from standard.company_main
        left join dict_city  on company_main.city_code = dict_city.city_code
    '''))
    rows = request.cursor_standard.fetchall()
    return json.dumps(rows, ensure_ascii=False)

"""
#根据填入的省市映射城市名称，传入参数即为data_source_province，data_source_city  ---》city_name
#1，三个字段都先去空格，并且保证都不为空字符串
#2，生成的企业名称必须在字典中存在必须完全匹配
#3，已经存在city_code字段的记录不做修改
"""
def updateCompanyCityFomProvinceAndArea(request):
    data_source_province = request.get("data_source_province").replace(" ","")
    data_source_city = request.get("data_source_city").replace(" ","")
    city_name = request.get("city_name").replace(" ","")
    if data_source_province == "" or data_source_city == "" or city_name == "" :
        print("输入的参数存在空格，不行")
        return "输入的参数存在空格，不行"
    
    request.set("final_city_name",city_name)
    request.mysql_52.execute(request.formatSql('''
        select city_code,city_name from standard.dict_city where city_name = '${city_name}'
    '''))
    rows = request.mysql_52.fetchall()
    if len(rows) == 0 :
        return "输入的最终城市名在字典中不存在，不行"
    request.set("old_province",data_source_province)
    request.set("old_city",data_source_city)
    city_code = rows[0]['city_code']
    request.set("city_code",city_code)
    request.mysql_52.execute(request.formatSql('''
        update standard.company_main 
        set city_code = '${city_code}'
        where province = '${old_province}'
        and city = '${old_city}'
        and( city_code is null  or city_code = '' )
    '''))
    effectRow = request.mysql_52.rowcount
    print("受影响行数：",effectRow)

    return "成功修改记录数："+str(effectRow)




